/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import Entity.Flights;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class FlightsBean {
    Collection Listfisght,searchfisght;
    Connect c=new Connect();
    Connection con;
    CallableStatement cl=null;
    public FlightsBean() {
    }
    private void closeCnn(){
        try {
            cl.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public Collection ListFights() {
        con=c.getConnection();
        Listfisght=new ArrayList();
        try {
            cl=con.prepareCall("{call ListFights_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                Flights fl=new Flights();
                fl.setFlightID(rs.getInt("FlightID"));
                fl.setNameCity(rs.getString("NameCity"));
                fl.setFlightName(rs.getString("FlightName"));
                fl.setDeparture_Time(rs.getString("Departure_Time"));
                fl.setArrival_Time(rs.getString("Arrival_Time"));
                fl.setDeparture_City(rs.getString("Departure_City"));
                fl.setArrival_City(rs.getString("Arrival_City"));
                fl.setPrice(rs.getFloat("Price"));
                Listfisght.add((Object)fl);
            }
            closeCnn();
        }catch (Exception ex){}
        return Listfisght;
    }
    //search flight
    public Collection SearchListFights(String departure,String arrival) {
        con=c.getConnection();
        searchfisght=new ArrayList();
        try {
            cl=con.prepareCall("{call SearchListFights_sp(?,?)}");
            cl.setString("departure",departure);
            cl.setString("arrival", arrival);
            ResultSet rs=cl.executeQuery();
            while(rs.next()) {
                Flights fl=new Flights();
                fl.setFlightID(rs.getInt("FlightID"));
                fl.setNameCity(rs.getString("NameCity"));
                fl.setFlightName(rs.getString("FlightName"));
                fl.setDeparture_Time(rs.getString("Departure_Time"));
                fl.setArrival_Time(rs.getString("Arrival_Time"));
                fl.setDeparture_City(rs.getString("Departure_City"));
                fl.setArrival_City(rs.getString("Arrival_City"));
                fl.setPrice(rs.getFloat("Price"));
                searchfisght.add((Object)fl);
            }
            closeCnn();
        }catch (Exception ex){closeCnn();}
        return searchfisght;
    }
    public boolean DeleteFlight(int id) {
        con=c.getConnection();
        try {
            cl=con.prepareCall("{call DeleteFlight_sp(?)}");
            cl.setInt(1,id);
            int rs=cl.executeUpdate();
            if(rs >0) {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        return false;
    }
    public boolean InsertFlight(int locationID,String flightName,String departure_Time,String arrival_Time,String deperture,String arrival_City,float price) {
        con=c.getConnection();
        try {
            cl=con.prepareCall("{call InsertFlight_sp(?,?,?,?,?,?,?)}");
            cl.setInt(1,locationID);
            cl.setString(2,flightName);
            cl.setString(3,departure_Time);
            cl.setString(4,arrival_Time);
            cl.setString(5,deperture);
            cl.setString(6,arrival_City);
            cl.setFloat(7,price);
            int rs=cl.executeUpdate();
            if(rs >0) {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        return false;
    }
    public boolean UpdateFlight(int locationID,String flightName,String departure_Time, String arrival_Time,String deperture,String arrival_City,float price,int flightID) {
        con=c.getConnection();
        try {
            cl=con.prepareCall("{call UpdateFlight_sp(?,?,?,?,?,?,?,?)}");
            cl.setInt("LocationID",locationID);
            cl.setString("FlightName",flightName);
            cl.setString("Departure_Time",departure_Time);
            cl.setString("Arrival_Time",arrival_Time);
            cl.setString("Departure_City",deperture);
            cl.setString("Arrival_City",arrival_City);
            cl.setFloat("Price",price);
            cl.setInt("flightID",flightID);
            int rs=cl.executeUpdate();
            if(rs >0) {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        return false;
    }
}
